use [master]
if exists (select * from master..sysdatabases where name='BDFinal_Sanjinez')
	drop database BDFinal_Sanjinez
go
	create database BDFinal_Sanjinez
go
use [BDFinal_Sanjinez]
if exists (select * from sys.tables where name = 'Persona' )
	drop table Persona
go
begin
	create table Persona(
		codigo varchar(15) primary key
		,nombre nvarchar(30) not null
		,apellido nvarchar(30) not null
		,telefono nvarchar(20) null
		,direccion nvarchar(200) null
		,CI varchar(7) not null
	)
end
--if not exists (select * from sys.key_constraints where name='pk_persona')
--begin
--	alter table Persona add constraint pk_persona primary key (codigo)
--end

insert into Persona(codigo,nombre,apellido,telefono,direccion,CI)
	select t.cod,pe.FirstName,pe.LastName,telefono=(
												select top(1)convert(nvarchar(20),pho.PhoneNumber) 
													from AdventureWorks2012.Person.PersonPhone as pho
													where pho.BusinessEntityID=pe.BusinessEntityID)
										,direccion=(
												select top(1)AddressLine1
													from AdventureWorks2012.Person.Address as addr
														join AdventureWorks2012.Person.BusinessEntityAddress as ab on addr.AddressID=ab.AddressID
													where ab.BusinessEntityID=pe.BusinessEntityID)
										,right(concat('0000000000000',pe.BusinessEntityID),7)
	from [AdventureWorks2012].person.person as pe
	outer apply(
					select concat(subpe.PersonType,'-',left(subpe.LastName,1),LEFT(subpe.firstName,1),'-',right(concat('0000000000000',subpe.BusinessEntityID),9)) as 'cod'
						from AdventureWorks2012.Person.Person as subpe
						where pe.BusinessEntityID=subpe.BusinessEntityID
				)as t

if exists(select * from sys.tables where name='Asistencia')
	drop table Asistencia
go
begin
	create table Asistencia(
		Id int
		,CI int
		,fecha datetime default(current_timestamp)
		,ingreso bit
		,fecha_regular datetime
	)
end
if not exists(select * from sys.sequences where name='seqAsis')
begin
	create sequence seqAsis as Int minvalue 1 cycle;
end
if not exists(select * from sys.objects where name='marcacion')
begin
	create procedure marcacion 
		@cedula varchar(7),
		@salida varchar(10) output
	as
	begin
		declare @reg datetime
		declare @des bit -- entrada 1 y salida 0
		case datepart(hour,current_timestamp)
			when 8 
				then set @reg=dateadd(hour,08,cast(cast(getdate() as date)as datetime))
					 set @des=1
			when 12 
				then set @reg=dateadd(hour,12,cast(cast(getdate() as date)as datetime))
					 set @des=0
			when 14 
				then set @reg=dateadd(hour,14,cast(cast(getdate() as date)as datetime))
					 set @des=1
			when 18 
				then set @reg=dateadd(hour,18,cast(cast(getdate() as date)as datetime))
					 set @des=0
			--else null
		insert into Asistencia(Id,CI,ingreso,fecha_regular)
			values (select next value for seqAsis,@cedula,@des,@reg)
		return 'entrando'
	end 

end

if not exists(select * from sys.objects where name='reporte')
begin
	create procedure reporte
		@cedula int
	as
	begin
		if cedulla isnull

	end
end
